##
#   Project: gmediacatalog - Catalog your media to find them later
#    Author: Fabio Castelli <muflone@vbsimple.net>
# Copyright: 2010 Fabio Castelli
#   License: GPL-2+
#  This program is free software; you can redistribute it and/or modify it
#  under the terms of the GNU General Public License as published by the Free
#  Software Foundation; either version 2 of the License, or (at your option)
#  any later version.
# 
#  This program is distributed in the hope that it will be useful, but WITHOUT
#  ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
#  FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
#  more details.
# 
# On Debian GNU/Linux systems, the full text of the GNU General Public License
# can be found in the file /usr/share/common-licenses/GPL-2.
##

import time
import sqlite3


class SqlBackEnd(object):
  SQL_INSERT_VOLUME = 'INSERT INTO volumes(' \
    'volume_name, volume_description, volume_added, volume_root, ' \
    'volume_category, volume_title, volume_folder) ' \
    'VALUES(?,?,?,?,?,?,?)'
  SQL_INSERT_PATH = 'INSERT INTO paths(paths_volume, paths_uri, paths_icon) ' \
    'VALUES(?,?,?)'
  SQL_INSERT_FILE = 'INSERT INTO files(' \
    'file_volume, file_path, file_name, file_size, file_type, ' \
    'file_content_type, file_icon, file_modification, file_access, '\
    'file_changed, file_user, file_group, file_mode, file_target, ' \
    'file_flags, file_description, file_category, file_extra) ' \
    'VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
  SQL_DELETE_VOLUME = 'DELETE FROM volumes WHERE volume_id=?'
  SQL_DELETE_PATHS = 'DELETE FROM paths WHERE paths_volume=?'
  SQL_DELETE_FILES = 'DELETE FROM files WHERE file_volume=?'

  def __init__(self, database):
    self.sqlConn = sqlite3.connect(database, check_same_thread = False)
    self.sqlConn.row_factory = sqlite3.Row

  def __del__(self):
    self.sqlConn.close()

  def commit(self):
    self.sqlConn.commit()

  def add_volume(self, name, description, uri, category_id, title, folder_id):
    curVolume = self.sqlConn.execute(self.SQL_INSERT_VOLUME, 
      (name, description, int(time.time()), uri, category_id, title, folder_id))
    volume_id = curVolume.lastrowid
    curVolume.close()
    return volume_id

  def add_path(self, volume_id, folder_path, icons):
    # Trim ending /
    if folder_path.endswith('/'):
      folder_path = folder_path[:-1]
    curFolder = self.sqlConn.execute(self.SQL_INSERT_PATH, (
      volume_id, folder_path, ';'.join(icons))
    )
    folder_id = curFolder.lastrowid
    curFolder.close()
    return folder_id

  def add_file(self, volume_id, folder_id, name, size, file_type, content_type,
               icons, modification_time, access_time, changed_time,
               owner, group, modes, target, flags, description, category_id,
               extra):
    curFile = self.sqlConn.execute(self.SQL_INSERT_FILE, (
      volume_id,                                    # Volume ID FK
      folder_id,                                    # Path ID FK
      name,                                         # File name
      size,                                         # Size
      file_type,                                    # File Type
      content_type,                                 # Content type
      icons,                                        # Icons list
      modification_time, access_time, changed_time, # Mod./access/changed time
      owner, group, modes,                          # Owner, group and modes
      target,                                       # Target
      flags,                                        # Flags (hidden)
      description,                                  # Description
      category_id,                                  # Category ID FK
      extra                                         # Extra info
    ))
    file_id = curFile.lastrowid
    curFile.close()
    return file_id

  def select_volumes(self):
    return self.sqlConn.execute(
      'SELECT volume_id, volume_name, volume_root ' \
      'FROM volumes ORDER BY volume_id'
    )

  def select_paths(self, volume_id):
    return self.sqlConn.execute(
      'SELECT paths_id, paths_uri, paths_icon ' \
      'FROM paths WHERE paths_volume=%d ' \
      'ORDER BY paths_uri' % volume_id
    )

  def select_files(self, file_path):
    return self.sqlConn.execute(
      'SELECT file_id, file_type, file_name, file_user, file_group, ' \
      'file_flags, file_mode, file_content_type, file_icon, file_size, ' \
      'file_modification, file_changed, file_access, file_target, ' \
      'file_description ' \
      'FROM files WHERE file_path=%d '\
      'ORDER BY file_type DESC, file_name' % file_path
    )

  def files_count(self, volume_id, path_uri):
    # Find subFolder paths_id
    curSubFolder = self.sqlConn.execute('SELECT paths_id FROM paths ' \
      'WHERE paths_volume=%d AND paths_uri="%s"' % (volume_id, path_uri)
    )
    rowSubFolder = curSubFolder.fetchone()
    if rowSubFolder is not None:
      curFilesCount = self.sqlConn.execute(
        'SELECT COUNT(*) AS count FROM files '\
        'WHERE file_path=%d' % rowSubFolder['paths_id']
      )
      count = curFilesCount.fetchone()['count']
      curFilesCount.close()
    else:
      # Folder not found
      count = -1
    curSubFolder.close()

    return count

  def delete_volume(self, volume_id):
    self.sqlConn.execute(self.SQL_DELETE_VOLUME, (volume_id, ))
    self.sqlConn.execute(self.SQL_DELETE_PATHS, (volume_id, ))
    self.sqlConn.execute(self.SQL_DELETE_FILES, (volume_id, ))
    self.commit()
